package com.dushibao.dao;

import cn.hutool.core.util.ObjectUtil;
import com.dushibao.common.Constants;
import com.dushibao.entity.Advert;
import com.dushibao.utils.DBUtils;
import com.dushibao.utils.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class AdvertDao {


    public Integer save(Advert advert) {

        try{
            String sql = "insert into dushibao_advert(name,url,width,height,images) values(?,?,?,?,?)";

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setObject(1,advert.getName());
            stmt.setObject(2,advert.getUrl());
            stmt.setObject(3,advert.getWidth());
            stmt.setObject(4,advert.getHeight());
            stmt.setObject(5,advert.getImages());

            stmt.executeUpdate();
            return Constants.SUCCESS;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }


    public int update(Advert advert)  {
        try{
            String sql = "update dushibao_advert set name=?,url=?,width=?,height=?,images=? where id=?";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setObject(1,advert.getName());
            stmt.setObject(2,advert.getUrl());
            stmt.setObject(3,advert.getWidth());
            stmt.setObject(4,advert.getHeight());
            stmt.setObject(5,advert.getImages());

            stmt.setLong(6,advert.getId());

            stmt.executeUpdate();
            return Constants.SUCCESS;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    public Page page(Page page , Map<String,Object> params) {

        try{
            StringBuilder querySql = new StringBuilder("select id,addTime,name,url,width,height,images from dushibao_advert where 1=1 ");

            if(ObjectUtil.isNotEmpty(params.get("startTime"))){
                querySql.append(" and addTime > '"+params.get("startTime")+"'");
            }

            if(ObjectUtil.isNotEmpty(params.get("endTime"))){
                querySql.append(" and addTime < '"+params.get("endTime")+"'");
            }

            querySql.append(" limit ?,? ");

            Connection conn = DBUtils.getConnection();

            PreparedStatement stmt = conn.prepareStatement(querySql.toString());
            stmt.setInt(1,page.getStart());
            stmt.setInt(2,page.getPageSize());

            ResultSet rs = stmt.executeQuery();

            List<Object> objs = new ArrayList<>();
            while(rs.next()){
                Advert advert = new Advert();
                advert.setId(rs.getLong("id"));
                advert.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                advert.setName(rs.getString("name"));
                advert.setUrl(rs.getString("url"));
                advert.setWidth(rs.getDouble("width"));
                advert.setHeight(rs.getDouble("height"));
                advert.setImages(rs.getString("images"));
                objs.add(advert);
            }

            DBUtils.close(rs,stmt,conn);

            StringBuilder countSql = new StringBuilder("select count(*) as total from dushibao_advert where 1=1 ");

            if(ObjectUtil.isNotEmpty(params.get("startTime"))){
                countSql.append(" and addTime > '"+params.get("startTime")+"'");
            }

            if(ObjectUtil.isNotEmpty(params.get("endTime"))){
                countSql.append(" and addTime < '"+params.get("endTime")+"'");
            }

            conn = DBUtils.getConnection();
            stmt = conn.prepareStatement(countSql.toString());

            rs = stmt.executeQuery();

            int total = 0;
            if(rs.next()){
                total = rs.getInt("total");
            }

            Page iPage = new Page();
            iPage.setCurrent(page.getCurrent());
            iPage.setPageSize(page.getPageSize());
            iPage.setData(objs);
            iPage.setPages(total % iPage.getPageSize()==0?total/ iPage.getPageSize():total/ iPage.getPageSize()+1);
            iPage.setTotal(total);

            return iPage;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    public Advert getById(long id) {
        try{
            String sql = "select id,addTime,name,url,width,height,images from dushibao_advert where id=? ";
            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setLong(1,id);
            ResultSet rs = stmt.executeQuery();
            Advert advert = new Advert();

            if(rs.next()){
                advert.setId(rs.getLong("id"));
                advert.setAddTime(rs.getTimestamp("addTime").toLocalDateTime());
                advert.setName(rs.getString("name"));
                advert.setUrl(rs.getString("url"));
                advert.setWidth(rs.getDouble("width"));
                advert.setHeight(rs.getDouble("height"));
                advert.setImages(rs.getString("images"));
            }

            return advert;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }


    public Integer deleteById(List<Long> ids) {
        try{

            StringBuilder sql = new StringBuilder(" delete from dushibao_advert where id in ( ");
            for (int i = 0; i < ids.size(); i++) {
                sql.append(ids.get(i));

                if(i<ids.size()-1){
                    sql.append(",");
                }

            }
            sql.append(")");

            Connection conn = DBUtils.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            return Constants.SUCCESS;
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
}
